package com.cennavi.mineplay.data;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.cennavi.mineplay.utils.DBUtils;
import com.mercator.TileUtils;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.WKTReader;

import no.ecc.vectortile.VectorTileEncoder;


@RestController
@RequestMapping("/audispeed")
public class AudiSpeedService {
	
	@Autowired
	protected JdbcTemplate jdbc;

	@RequestMapping("/findroad")
	public Object findRoad(@RequestParam("cityName") String cityName ) {
		String sql ="SELECT DISTINCT english_name, road_level,false as disabledV FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
				"WHERE city='"+cityName+"' AND road_name not like '%立交%' AND road_name not like '%互通%' GROUP BY english_name,road_level ORDER BY road_level DESC" ;
		
		try {
			List<Map<String, Object>> results = jdbc.queryForList(sql);
			List<Map<String, Object>> nresults = new ArrayList<Map<String, Object>>();
			Map<String, Object> expressmap = new HashMap<String, Object>();
			expressmap.put("value", "AllExpressway");
			expressmap.put("label", "AllExpressway");
			expressmap.put("road_level", 2);
			expressmap.put("disabledV", false);
			nresults.add(expressmap);
			for (Map<String, Object> m: results) {
				if(m.get("road_level").equals(2)) {
					Map<String, Object> express = new HashMap<String, Object>();
					express.put("value", m.get("english_name"));
					express.put("label", m.get("english_name"));
					express.put("road_level", m.get("road_level"));
					express.put("disabledV", false);
					nresults.add(express);
				}
				}
			Map<String, Object> highmap = new HashMap<String, Object>();
			highmap.put("value", "AllHighway");
			highmap.put("label", "AllHighway");
			highmap.put("road_level", 1);
			highmap.put("disabledV", false);
			nresults.add(highmap);
			for (Map<String, Object> m: results) {
				if(m.get("road_level").equals(1)) {
					Map<String, Object> high = new HashMap<String, Object>();
					high.put("value", m.get("english_name"));
					high.put("label", m.get("english_name"));
					high.put("road_level", m.get("road_level"));
					high.put("disabledV", false);
					nresults.add(high);
					}
			}
			
			return nresults ;
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return null;
		
	}
	
	
	
	@RequestMapping(value="/selectmultiroad")
	public Object multiRoad(String area, String cityName,  String week, String time,String sDate, String eDate  ) {
	
		if(time.length() == 1) {
			 time = "0"+time;
		}
		String sql ="with a1 as " + 
				"(SELECT A .english_name, A .rtic_id, AVG (speed) AS avg_speed, b.road_level,'"+time+"' AS HOUR,a.geom " + 
				"FROM audi_speed_road A JOIN audi_speed_road_data b ON A .rtic_id = b.rtic_id " + 
				"WHERE A .city = '"+cityName+"' AND  week = '"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' AND SUBSTRING (DATA, 9, 11) = '"+time+"' " + 
				"GROUP BY english_name, A .rtic_id, road_level,geom) " + 
				"select a1.english_name,a1.rtic_id,a1.avg_speed,a1.road_level,a1.hour," + 
				"	st_asgeojson(a1.geom) AS geojson from  a1 where  st_intersects ( " + 
				"	a1.geom,st_setsrid(st_geomfromgeojson ('"+area+"'), 4326) " + 
				")";
		
		try {
			Object value = DBUtils.getGeojson(jdbc, sql);
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	@RequestMapping(value="/colrtic/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] rticPbf(@PathVariable int x, @PathVariable int y, @PathVariable int z, String cityName, String engName, String week, String time,String sDate, String eDate  ) {
		String sql = "";
		String level;
		String[] splits = engName.split(",");
		if(time.length() == 1) {
			 time = "0"+time;
		}
		StringBuilder sb = new StringBuilder();
		
		for(String s:splits) {
			sb.append("'");
			sb.append(s);
			sb.append("',");
		}
		String str = sb.substring(0,sb.length()-1);
		
		if (engName.toLowerCase().contains("allexpressway") || engName.toLowerCase().contains("allhighway")) {
			if (engName.toLowerCase().equals("allexpressway")) {
				level = "2";
			} else if(engName.toLowerCase().equals("allhighway")){
				level = "1";
			} else {
				level = "1,2";
			}
			sql="with a1 as " + 
					"(SELECT A .english_name, A .rtic_id, AVG (speed) AS avg_speed, b.road_level,'"+time+"' AS HOUR,a.geom " + 
					"FROM audi_speed_road A JOIN audi_speed_road_data b ON A .rtic_id = b.rtic_id " + 
					"WHERE A .city = '"+cityName+"' AND road_level in ( "+level+") AND week = '"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' AND SUBSTRING (DATA, 9, 11) = '"+time+"' " + 
					"GROUP BY english_name, A .rtic_id, road_level,geom) " + 
					"select a1.english_name,a1.rtic_id,a1.avg_speed,a1.road_level,a1.hour,st_astext(a1.geom) AS geom from  a1 where  st_intersects ( " + 
					"	a1.geom,st_geomfromtext (?, 4326) " + 
					")";
		} 
		else {
			sql ="with a1 as " + 
					"(SELECT A .english_name, A .rtic_id, AVG (speed) AS avg_speed, b.road_level,'"+time+"' AS HOUR,a.geom " + 
					"FROM audi_speed_road A JOIN audi_speed_road_data b ON A .rtic_id = b.rtic_id " + 
					"WHERE A .city = '"+cityName+"' AND english_name IN ("+str+") AND week = '"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' AND SUBSTRING (DATA, 9, 11) = '"+time+"' " + 
					"GROUP BY english_name, A .rtic_id, road_level,geom) " + 
					"select a1.english_name,a1.rtic_id,a1.avg_speed,a1.road_level,a1.hour," + 
					"	st_astext(a1.geom) AS geom from  a1 where  st_intersects ( " + 
					"	a1.geom,st_geomfromtext (?, 4326) " + 
					")";
		}
		
		try {
			String tile = TileUtils.parseXyz2Bound(x, y, z);
			
			List<Map<String, Object>> results = jdbc.queryForList(sql, tile);
		
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

			for (Map<String, Object> m : results) {
				
				String wkt = (String) m.get("geom");
				
				Geometry geom = new WKTReader().read(wkt);
				
				m.remove("geom");

				TileUtils.convert2Piexl(x, y, z, geom);
				
				vte.addFeature("position", m, geom);

			}

			return vte.encode();
			
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	@RequestMapping("/roadratio")
	public Object roadRatio(String cityName, String engName, String week, String stime, String etime, String sDate, String eDate  ) {
		String sql1 = "";
		String sql2 ="";
		String strSingle ="";
		int level = 2;
		if(stime.length()==1) {
			 stime = "0"+stime;
		} 
		if (etime.length()==1) {
			etime = "0"+etime;
		}
		String[] splits = engName.split(",");
		List<Object> list = new ArrayList<>();
		List<String> allList = new ArrayList<>();
		List<String> singleList = new ArrayList<>();
		for(int i = 0; i<splits.length; i++) {
			if(splits[i].equals("AllExpressway") || splits[i].equals("AllHighway")) {
				allList.add(splits[i]);
			}else {
				singleList.add(splits[i]);
			}
		}
		if(!allList.isEmpty()||allList.size()!=0) {
				if(allList.size()==1) {
				  if(allList.contains("AllHighway")) {
					level = 1;
				} else if(allList.contains("AllExpressway")) 
				{
					level = 2;
				}
				} 
				Collections.sort(allList);
		}
		if(!singleList.isEmpty()||singleList.size()!=0) {
			StringBuilder sbSingle = new StringBuilder();
			for(String s:singleList) {
				sbSingle.append("'");
				sbSingle.append(s);
				sbSingle.append("',");
			}
			strSingle = sbSingle.substring(0, sbSingle.length()-1);
		}
		if(!allList.isEmpty()||allList.size()!=0) {
			for (String s: allList) {
				Map<String, Map<Double, Double>> m1 = new HashMap<>();
				List<ArrayList<Object>> outlist = new ArrayList<ArrayList<Object>>();
				Map<String, Object> map = new LinkedHashMap<>();
				sql1 = "with a1 as ( " + 
					"SELECT sum(to_number(len,'99.99')) sum_fenmu FROM audi_speed_road c WHERE c.rtic_id in( " + 
					"	SELECT DISTINCT a.rtic_id FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
					"	WHERE a.city='"+cityName+"' AND b.road_level = "+level+" AND speed<120 " + 
					"	) AND c.city='"+cityName+"' " + 
					"), " + 
					"a2 as " + 
					"( " + 
					" SELECT sum(to_number(len,'99.99')) sum_fenzi,b.avg_speed FROM audi_speed_road c,( " + 
					" SELECT c.rtic_id, cast((avg_speed-5) / 10 as int) * 10 + 5 as avg_speed  FROM " + 
					"(SELECT a.rtic_id, avg(speed) as avg_speed FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
					"WHERE a.city='"+cityName+"' AND b.road_level ="+level+" AND week ='"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' AND b.speed<120 " + 
					"AND substring(data,9,11) BETWEEN '"+stime+"' AND '"+etime+"' GROUP BY a.rtic_id ORDER BY avg_speed ) as c " + 
					" ) as b where c.rtic_id=b.rtic_id group by b.avg_speed " + 
					") " + 
					"select '"+s+"'  as road_name,a2.avg_speed, cast ((sum_fenzi / sum_fenmu) as decimal(18,4)) ratio from a1,a2 ORDER BY a2.avg_speed ";
				m1.put(s, new LinkedHashMap<Double, Double>());
				level = 1;
				try {
					List<Map<String, Object>> results1 = jdbc.queryForList(sql1);
					for (Map<String, Object> m : results1) {
						double speed = Double.parseDouble(m.get("avg_speed").toString());
						double ratio = Double.parseDouble(m.get("ratio").toString());
						String name = m.get("road_name").toString();
						Map<Double, Double> m2 = m1.get(name);
						m2.put(speed, ratio);
					}
					Iterator<Entry<String, Map<Double, Double>>> it = m1.entrySet().iterator();
					while(it.hasNext()) {
						Entry<String, Map<Double, Double>> en = it.next();
						String key = en.getKey();
						map.put("road", key);
						Map<Double, Double> value = en.getValue();
						Iterator<Entry<Double, Double>> it2 = value.entrySet().iterator();
						for (int j =0; j < 12; j++) {
							ArrayList<Object> inlist = new ArrayList<>();
							inlist.add(j*10);
							inlist.add((j+1)*10);
							inlist.add(0);
							outlist.add(inlist);
						}
						while(it2.hasNext()) {
							Entry<Double, Double> en2 = it2.next();
							Double speed = en2.getKey();
							Double ratio = (double)Math.round(en2.getValue()*100*100)/100;
							int k = (int)(speed/10);
							outlist.get(k).set(2, ratio);
						}
						map.put("data", outlist);
					}
				} catch(Exception e) {
					e.printStackTrace();
				}
				list.add(map);
			}
		}
		if (strSingle!="") {
			sql2 = "with a1 as ( " + 
					"SELECT sum(to_number(len,'99.99')) sum_fenmu,english_name FROM audi_speed_road WHERE city='"+cityName+"' and english_name " + 
					"in ("+strSingle+") GROUP BY english_name ) ," + 
					"a2 as(" + 
					" SELECT sum(to_number(len,'99.99')) sum_fenzi,b.avg_speed,english_name FROM audi_speed_road c,(" + 
					" SELECT c.rtic_id,cast((avg_speed-5) / 10 as int) * 10 + 5 as avg_speed  FROM " + 
					"(SELECT a.rtic_id, avg(speed) as avg_speed FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
					"WHERE a.city='"+cityName+"' AND english_name in ("+strSingle+") AND week = '"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' "+ 
					"AND substring(data,9,11) BETWEEN '"+stime+"' AND '"+etime+"' GROUP BY a.rtic_id ) as c " + 
					" ) as b where c.rtic_id=b.rtic_id group by b.avg_speed,english_name ORDER BY english_name " + 
					")" + 
					"select a1.english_name, a2.avg_speed,cast (sum_fenzi / sum_fenmu as DECIMAL(18,4)) ratio from a1 join  a2 ON a1.english_name=a2.english_name WHERE 1=1 " + 
					" GROUP BY a1.english_name,a2.avg_speed,ratio " + 
					"ORDER BY a1.english_name";
			
			try {
				List<Map<String,Object>> results2 = jdbc.queryForList(sql2);
				Map<String, Map<Double, Double>> m1 = new HashMap<>();
				for(String s:singleList) {
					m1.put(s, new HashMap<Double, Double>());				
					}
				for(Map<String, Object> m :results2) {
					double speed = Double.parseDouble(m.get("avg_speed").toString());
					double ratio = Double.parseDouble(m.get("ratio").toString());
					String name =  m.get("english_name").toString();
					Map<Double, Double> m2 = m1.get(name);
					m2.put(speed, ratio);
				}
				
				Iterator<Entry<String, Map<Double, Double>>> it = m1.entrySet().iterator();
				while(it.hasNext()) {
					Map<String, Object> map = new LinkedHashMap<>();
					List<ArrayList<Object>> outlist = new ArrayList<ArrayList<Object>>();
					Entry<String, Map<Double, Double>> en = it.next();
					String key = en.getKey();
					map.put("road", key);
					Map<Double, Double> value = en.getValue();
					Iterator<Entry<Double, Double>> it2 = value.entrySet().iterator();
					for (int j =0; j < 12; j++) {
						ArrayList<Object> inlist = new ArrayList<>();
						inlist.add(j*10);
						inlist.add((j+1)*10);
						inlist.add(0);
						outlist.add(inlist);
					}
					while(it2.hasNext()) {
						Entry<Double, Double> en2 = it2.next();
						Double speed = en2.getKey();
						Double ratio = (double)Math.round(en2.getValue()*100*100)/100;
						int k = (int)(speed/10);
						outlist.get(k).set(2, ratio);
					}
					map.put("data", outlist);
					list.add(map);
				}
				
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		return list;
	}
	
	@RequestMapping("/multiroadratio")
	public Object multiRoadRatio(String cityName, String week, String stime, String etime, String area, String sDate, String eDate  ) {
		if(stime.length()==1) {
			 stime = "0"+stime;
		} 
		if (etime.length()==1) {
			etime = "0"+etime;
		}
		List<Object> list = new ArrayList<>();
		List<Object> roadList = new ArrayList<>();
		try {
			String sql1 = "SELECT DISTINCT english_name FROM audi_speed_road WHERE st_intersects (geom,st_setsrid (st_geomfromgeojson ('"+area+"'),4326))";
			 List<Map<String, Object>> results1 = jdbc.queryForList(sql1);
			 for(Map<String, Object> m: results1) {
				 roadList.add(m.get("english_name"));
			 }
			  
			  for (int i = 0; i <roadList.size() ; i++) {
				List<List<Object>> outlist = new ArrayList<List<Object>>();
				for (int j =0; j < 12; j++) {
					List<Object> inlist = new ArrayList<>();
					inlist.add(j*10);
					inlist.add((j+1)*10);
					inlist.add(0);
					outlist.add(inlist);
				}
				String sql2 ="with a1 as ( " + 
						"SELECT sum(to_number(len,'99.99')) sum_fenmu FROM audi_speed_road WHERE city='"+cityName+"' AND english_name='"+roadList.get(i)+"' AND st_intersects(geom,st_setsrid(st_geomfromgeojson ('"+area+"'), 4326)) ), " + 
						"a2 as( " + 
						"with tmp1 as (select rtic_id from audi_speed_road as A where A .city = '"+cityName+"' AND english_name='"+roadList.get(i)+"' AND st_intersects(geom,st_setsrid(st_geomfromgeojson ('"+area+"'), 4326))), " + 
						"						tmp111 as (select rtic_id,speed from audi_speed_road_data where week = '"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' AND SUBSTRING (DATA, 9, 11) BETWEEN '"+stime+"' AND '"+etime+"')," + 
						"						tmp11 as (SELECT A .rtic_id,AVG (speed) AS avg_speed FROM tmp1 A JOIN tmp111 b ON A .rtic_id = b.rtic_id GROUP BY A .rtic_id) ," + 
						"						tmp2 as (SELECT C .rtic_id,CAST (avg_speed / 10 AS INT) * 10 + 5 AS avg_speed FROM tmp11 AS C) SELECT SUM (to_number(len, '99.99')) sum_fenzi, b.avg_speed " + 
						"						FROM audi_speed_road C,tmp2  b WHERE C .rtic_id = b.rtic_id GROUP BY b.avg_speed) " + 
						"select '"+roadList.get(i)+"' as english_name, a2.avg_speed,cast ((sum_fenzi / sum_fenmu) as decimal(18,4)) ratio from a1,a2  ORDER BY a2.avg_speed " ;
				
				 Map<String, Object> map = new HashMap<>();
				 map.put("road",roadList.get(i));
				 List<Map<String, Object>> results2 = jdbc.queryForList(sql2);
				 for(Map<String, Object> m:results2) {
					 Integer speed =Integer.parseInt(m.get("avg_speed").toString());
					 Double ratio = (double)Math.round(Double.parseDouble(m.get("ratio").toString())*100*100)/100;
					 int k = (int)(speed/10);
					 outlist.get(k).set(2, ratio);
				 }
				 map.put("data", outlist);
				 list.add(map);
			  }
			return list;
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		
		return null;
	}
	
	@RequestMapping(value = "/ratiocsv", method = RequestMethod.GET)  
	public ResponseEntity<byte[]> downloadRatioCSV(String cityName, String engName, String week, String stime, String etime,String sDate, String eDate) throws IOException {  
		String sql1 = "";
		String sql2 ="";
		String strSingle = "";
		int level = 2;
		if(stime.length()==1) {
			 stime = "0"+stime;
		} 
		if (etime.length()==1) {
			etime = "0"+etime;
		}
		String[] splits = engName.split(",");
		List<Map<String, Object>> list = new ArrayList<>();
		List<String> allList = new ArrayList<>();
		List<String> singleList = new ArrayList<>();
		for(int i = 0; i<splits.length; i++) {
			if(splits[i].equals("AllExpressway") || splits[i].equals("AllHighway")) {
				allList.add(splits[i]);
			}else {
				singleList.add(splits[i]);
			}
		}
		if(!allList.isEmpty()||allList.size()!=0) {
				if(allList.size()==1) {
				  if(allList.contains("AllHighway")) {
					level = 1;
				} else if(allList.contains("AllExpressway")) 
				{
					level = 2;
				}
				} 
				Collections.sort(allList);
		}
		if(!singleList.isEmpty()||singleList.size()!=0) {
			StringBuilder sbSingle = new StringBuilder();
			for(String s:singleList) {
				sbSingle.append("'");
				sbSingle.append(s);
				sbSingle.append("',");
			}
			strSingle = sbSingle.substring(0, sbSingle.length()-1);
		}
		if(!allList.isEmpty()||allList.size()!=0) {
			for (String s: allList) {
				Map<String, Map<Double, Double>> m1 = new HashMap<>();
				List<ArrayList<Object>> outlist = new ArrayList<ArrayList<Object>>();
				Map<String, Object> map = new LinkedHashMap<>();
				sql1 = "with a1 as ( " + 
					"SELECT sum(to_number(len,'99.99')) sum_fenmu FROM audi_speed_road c WHERE c.rtic_id in( " + 
					"	SELECT DISTINCT a.rtic_id FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
					"	WHERE a.city='"+cityName+"' AND b.road_level = "+level+" AND speed<120 " + 
					"	) AND c.city='"+cityName+"' " + 
					"), " + 
					"a2 as " + 
					"( " + 
					" SELECT sum(to_number(len,'99.99')) sum_fenzi,b.avg_speed FROM audi_speed_road c,( " + 
					" SELECT c.rtic_id, cast((avg_speed-5)/ 10 as int) * 10 + 5 as avg_speed  FROM " + 
					"(SELECT a.rtic_id, avg(speed) as avg_speed FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
					"WHERE a.city='"+cityName+"' AND b.road_level ="+level+" AND week ='"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' AND b.speed<120 " + 
					"AND substring(data,9,11) BETWEEN '"+stime+"' AND '"+etime+"' GROUP BY a.rtic_id ORDER BY avg_speed ) as c " + 
					" ) as b where c.rtic_id=b.rtic_id group by b.avg_speed " + 
					") " + 
					"select '"+s+"'  as road_name,a2.avg_speed, cast ((sum_fenzi / sum_fenmu) as decimal(18,4)) ratio from a1,a2 ORDER BY a2.avg_speed ";
				m1.put(s, new LinkedHashMap<Double, Double>());
				level = 1;
				try {
					List<Map<String, Object>> results1 = jdbc.queryForList(sql1);
					for (Map<String, Object> m : results1) {
						double speed = Double.parseDouble(m.get("avg_speed").toString());
						double ratio = Double.parseDouble(m.get("ratio").toString());
						String name = m.get("road_name").toString();
						Map<Double, Double> m2 = m1.get(name);
						m2.put(speed, ratio);
					}
					Iterator<Entry<String, Map<Double, Double>>> it = m1.entrySet().iterator();
					while(it.hasNext()) {
						Entry<String, Map<Double, Double>> en = it.next();
						String key = en.getKey();
						map.put("road", key);
						Map<Double, Double> value = en.getValue();
						Iterator<Entry<Double, Double>> it2 = value.entrySet().iterator();
						for (int j =0; j < 12; j++) {
							ArrayList<Object> inlist = new ArrayList<>();
							inlist.add(j*10);
							inlist.add((j+1)*10);
							inlist.add(0);
							outlist.add(inlist);
						}
						while(it2.hasNext()) {
							Entry<Double, Double> en2 = it2.next();
							Double speed = en2.getKey();
							Double ratio = (double)Math.round(en2.getValue()*100*100)/100;
							int k = (int)(speed/10);
							outlist.get(k).set(2, ratio);
						}
						map.put("data", outlist);
					}
				} catch(Exception e) {
					e.printStackTrace();
				}
				list.add(map);
			}
		}
		if (strSingle!="") {
			sql2 = "with a1 as ( " + 
					"SELECT sum(to_number(len,'99.99')) sum_fenmu,english_name FROM audi_speed_road WHERE city='"+cityName+"' and english_name " + 
					"in ("+strSingle+") GROUP BY english_name ) ," + 
					"a2 as(" + 
					" SELECT sum(to_number(len,'99.99')) sum_fenzi,b.avg_speed,english_name FROM audi_speed_road c,(" + 
					" SELECT c.rtic_id,cast((avg_speed-5) / 10 as int) * 10 + 5 as avg_speed  FROM " + 
					"(SELECT a.rtic_id, avg(speed) as avg_speed FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
					"WHERE a.city='"+cityName+"' AND english_name in ("+strSingle+") AND week = '"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' " + 
					"AND substring(data,9,11) BETWEEN '"+stime+"' AND '"+etime+"' GROUP BY a.rtic_id ) as c " + 
					" ) as b where c.rtic_id=b.rtic_id group by b.avg_speed,english_name ORDER BY english_name " + 
					")" + 
					"select a1.english_name, a2.avg_speed,cast (sum_fenzi / sum_fenmu as DECIMAL(18,4)) ratio from a1 join  a2 ON a1.english_name=a2.english_name WHERE 1=1 " + 
					" GROUP BY a1.english_name,a2.avg_speed,ratio " + 
					"ORDER BY a1.english_name";
			
			try {
				List<Map<String,Object>> results2 = jdbc.queryForList(sql2);
				Map<String, Map<Double, Double>> m1 = new HashMap<>();
				for(String s:singleList) {
					m1.put(s, new HashMap<Double, Double>());				
					}
				for(Map<String, Object> m :results2) {
					double speed = Double.parseDouble(m.get("avg_speed").toString());
					double ratio = Double.parseDouble(m.get("ratio").toString());
					String name =  m.get("english_name").toString();
					Map<Double, Double> m2 = m1.get(name);
					m2.put(speed, ratio);
				}
				
				Iterator<Entry<String, Map<Double, Double>>> it = m1.entrySet().iterator();
				while(it.hasNext()) {
					Map<String, Object> map = new LinkedHashMap<>();
					List<ArrayList<Object>> outlist = new ArrayList<ArrayList<Object>>();
					Entry<String, Map<Double, Double>> en = it.next();
					String key = en.getKey();
					map.put("road", key);
					Map<Double, Double> value = en.getValue();
					Iterator<Entry<Double, Double>> it2 = value.entrySet().iterator();
					for (int j =0; j < 12; j++) {
						ArrayList<Object> inlist = new ArrayList<>();
						inlist.add(j*10);
						inlist.add((j+1)*10);
						inlist.add(0);
						outlist.add(inlist);
					}
					while(it2.hasNext()) {
						Entry<Double, Double> en2 = it2.next();
						Double speed = en2.getKey();
						Double ratio = (double)Math.round(en2.getValue()*100*100)/100;
						int k = (int)(speed/10);
						outlist.get(k).set(2, ratio);
					}
					map.put("data", outlist);
					list.add(map);
				}
				
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
			   HttpHeaders h = new HttpHeaders();  
			   h.add("Content-Type", "text/csv; charset=utf-8");  
			   h.setContentDispositionFormData("filename", "roadratio.csv");
			   StringBuilder sBuilder = new StringBuilder();
			   sBuilder.append("CityName,RoadName,Weekday,StartTime,EndTime,0-10km/h,10-20km/h,20-30km/h,30-40km/h,40-50km/h,50-60km/h,60-70km/h,70-80km/h,80-90km/h,90-100km/h,100-110km/h,110-120km/h");
			   sBuilder.append("\n");
			   for(int i = 0; i < list.size();i++ ) {
				   	   sBuilder.append(cityName);
					   sBuilder.append(",");
					   sBuilder.append(list.get(i).get("road"));
					   sBuilder.append(",");
					   sBuilder.append(week);
					   sBuilder.append(",");
					   sBuilder.append(stime);
					   sBuilder.append(",");
					   sBuilder.append(etime);
					   sBuilder.append(",");
					   for (int j = 0; j < ((List<List>)list.get(i).get("data")).size(); j++) {
						   sBuilder.append(((List<List>)list.get(i).get("data")).get(j).get(2));
						   sBuilder.append(",");
					   }
					   sBuilder.append("\n");
					  
			   }
			   return new ResponseEntity<>(sBuilder.toString().getBytes("utf-8"), h, HttpStatus.OK);  
	}  
	
	
	@RequestMapping("/avgspeed")
	public Object avgSpeed(String cityName, String engName, String week,String sDate, String eDate) {
		String sql1 = "";
		String sql2 ="";
		String level="1";
		String[] splits = engName.split(",");
		String strAll="";
		String strSingle="";
		List<String> allList = new ArrayList<>();
		List<String> singleList = new ArrayList<>();
		for(int i = 0; i<splits.length; i++) {
			if(splits[i].equals("AllExpressway") || splits[i].equals("AllHighway")) {
				allList.add(splits[i]);
			}else {
				singleList.add(splits[i]);
			}
		}
		if(!allList.isEmpty()||allList.size()!=0) {
			StringBuilder sbAll = new StringBuilder();
			if(allList.size()==2) {
				level = "1,2";
			} else if(allList.contains("AllExpressway")) {
				level = "2";
			} else if (allList.contains("AllHighway")) {
				level = "1";
			}
			for(String s:allList) {
					sbAll.append("'");
					sbAll.append(s);
					sbAll.append("',");
			}
			strAll = sbAll.substring(0,sbAll.length()-1);
		} 
		if(!singleList.isEmpty()||singleList.size()!=0) {
			StringBuilder sbSingle = new StringBuilder();
			for(String s:singleList) {
				sbSingle.append("'");
				sbSingle.append(s);
				sbSingle.append("',");
			}
			strSingle = sbSingle.substring(0, sbSingle.length()-1);
		}
		List<Object> namelist = new ArrayList<Object>();
		List<List<Double>> datas = new ArrayList<>();
		List<Object> axisText = new ArrayList<Object>();
		
		int idx = 0;
		for(int i =0; i < 24;i++) {
			axisText.add(i+":00");
		}
		
		if (strAll!="") {
			sql1 = "SELECT CAST(AVG(speed) as decimal(18, 2)) as avg_speed,case when b.road_level = 1 then 'AllHighway' else 'AllExpressway' end AS english_name, substring(b.data,9,11) as hour " + 
					"FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
					"WHERE b.road_level IN ("+level+") AND week='"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' " + 
					"AND a.city='"+cityName+"' GROUP BY b.road_level,substring(b.data,9,11) ORDER BY english_name,HOUR";
			
			try {
				List<Map<String, Object>> results1 = jdbc.queryForList(sql1);
				Map<String,Map<String,Double>> m1 = new LinkedHashMap<>();
				
				for(String s:allList) {
					m1.put(s, new LinkedHashMap<String,Double>());
				}
				
				
				for(Map<String,Object> m : results1) {
					String name = m.get("english_name").toString();
					
					double speed = Double.parseDouble(m.get("avg_speed").toString());
					
					String hour = m.get("hour").toString();
					
					Map<String,Double> m2 = m1.get(name);
					
					m2.put(hour, speed);
					
				}
				for(int i=0;i<allList.size();i++) {
					datas.add(new ArrayList<Double>());
				}
				
				Iterator<Entry<String, Map<String, Double>>> it = m1.entrySet().iterator();
				
				while(it.hasNext()) {
					Entry<String, Map<String, Double>> en = it.next();
					String key = en.getKey();
					
					namelist.add(key);
					
					Map<String, Double> value = en.getValue();
					
					Iterator<Entry<String, Double>> it2 = value.entrySet().iterator();
					
					while(it2.hasNext()) {
						Entry<String, Double> en2 = it2.next();
						double avg_speed = en2.getValue();
						datas.get(idx).add(avg_speed);
					}
					idx++;
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		if(strSingle!="") {
			sql2 = " SELECT CAST(AVG(speed) as decimal(18, 2)) as avg_speed,b.english_name, substring(a.data,9,11) as hour " + 
					"FROM audi_speed_road_data a JOIN audi_speed_road b ON a.rtic_id=b.rtic_id " + 
					"WHERE b.english_name IN("+strSingle+") AND week='"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' " + 
					"AND b.city='"+cityName+"' GROUP BY b.english_name,substring(a.data,9,11) ORDER BY english_name, HOUR"; 
			try {
				List<Map<String, Object>> results2 = jdbc.queryForList(sql2);
				
				Map<String,Map<String,Double>> m1 = new LinkedHashMap<>();
				
				for(String s:singleList) {
					m1.put(s, new LinkedHashMap<String,Double>());
				}
				
				
				for(Map<String,Object> m : results2) {
					String name = m.get("english_name").toString();
					
					double speed = Double.parseDouble(m.get("avg_speed").toString());
					
					String hour = m.get("hour").toString();
					
					Map<String,Double> m2 = m1.get(name);
					
					m2.put(hour, speed);
					
				}
				for(int i=0;i<singleList.size();i++) {
					datas.add(new ArrayList<Double>());
				}
				
				
				Iterator<Entry<String, Map<String, Double>>> it = m1.entrySet().iterator();
				
				while(it.hasNext()) {
					Entry<String, Map<String, Double>> en = it.next();
					String key = en.getKey();
					
					namelist.add(key);
					
					Map<String, Double> value = en.getValue();
					
					Iterator<Entry<String, Double>> it2 = value.entrySet().iterator();
					
					while(it2.hasNext()) {
						Entry<String, Double> en2 = it2.next();
						double avg_speed = en2.getValue();
						datas.get(idx).add(avg_speed);
					}
					idx++;
				}
				
			
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("name", namelist);
		map.put("data", datas);
		map.put("axisText", axisText);
		return map;
	}
	
	
	
	
	@RequestMapping("/multiavgspeed")
	public Object multiAvgSpeed(String cityName, String week, String area,String sDate, String eDate) {
			 
			 List<Object> roadList = new ArrayList<>();
		     String sql1 = "SELECT DISTINCT english_name FROM audi_speed_road WHERE st_intersects (geom,st_setsrid (st_geomfromgeojson ('"+area+"'),4326)) ORDER BY english_name";
		     List<Map<String, Object>> results1 = jdbc.queryForList(sql1);
		     for (Map<String, Object> m:results1) {
		    	 roadList.add(m.get("english_name"));
		     }
		     
			 String sql2 = " SELECT CAST(AVG(speed) as decimal(18, 2)) as avg_speed,b.english_name, substring(a.data,9,11) as hour " + 
					"FROM audi_speed_road_data a JOIN audi_speed_road b ON a.rtic_id=b.rtic_id " + 
					"WHERE st_intersects(geom,st_setsrid(st_geomfromgeojson ('"+area+"'), 4326))  AND week='"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' " + 
					"AND b.city='"+cityName+"' GROUP BY b.english_name,substring(a.data,9,11) ORDER BY english_name, HOUR"; 
		
		try {
			List<Map<String, Object>> results2 = jdbc.queryForList(sql2);
			List<Object> axisText = new ArrayList<Object>();
			
			Map<String,Map<String,Double>> m1 = new LinkedHashMap<>();
			
			for(int i = 0; i<roadList.size(); i++) {
				m1.put(roadList.get(i).toString(), new LinkedHashMap<String,Double>());
			}
			
			
			for(Map<String,Object> m : results2) {
				String name = m.get("english_name").toString();
				
				double speed = Double.parseDouble(m.get("avg_speed").toString());
				
				String hour = m.get("hour").toString();
				
				Map<String,Double> m2 = m1.get(name);
				
				m2.put(hour, speed);
				
			}
			
			
			List<List<Double>> datas = new ArrayList<>();
			
			for(int i=0;i<roadList.size();i++) {
				datas.add(new ArrayList<Double>());
			}
			
			int idx = 0;
			
			Iterator<Entry<String, Map<String, Double>>> it = m1.entrySet().iterator();
			
			while(it.hasNext()) {
				Entry<String, Map<String, Double>> en = it.next();
				
				Map<String, Double> value = en.getValue();
				
				Iterator<Entry<String, Double>> it2 = value.entrySet().iterator();
				
				while(it2.hasNext()) {
					Entry<String, Double> en2 = it2.next();
					double avg_speed = en2.getValue();
					datas.get(idx).add(avg_speed);
				}
				idx++;
			}
			
			for(int i =0; i < 24;i++) {
				axisText.add(i+":00");
			}
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("name", roadList);
			map.put("data", datas);
			map.put("axisText", axisText);
			return map;
			
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	@RequestMapping(value = "/speedcsv", method = RequestMethod.GET)  
	public ResponseEntity<byte[]> downloadSpeedCSV(String cityName, String engName, String week,String sDate, String eDate) throws IOException {  
		String sql1 = "";
		String sql2 ="";
		String level="1";
		String[] splits = engName.split(",");
		String strAll = "";
		String strSingle = "";
		List<String> allList = new ArrayList<>();
		List<String> singleList = new ArrayList<>();
		for(int i = 0; i<splits.length; i++) {
			if(splits[i].equals("AllExpressway") || splits[i].equals("AllHighway")) {
				allList.add(splits[i]);
			}else {
				singleList.add(splits[i]);
			}
		}
		if(!allList.isEmpty()||allList.size()!=0) {
			StringBuilder sbAll = new StringBuilder();
			if(allList.size()==2) {
				level = "1,2";
			} else if(allList.contains("AllExpressway")) {
				level = "2";
			} else if (allList.contains("AllHighway")) {
				level = "1";
			}
			for(String s:allList) {
					sbAll.append("'");
					sbAll.append(s);
					sbAll.append("',");
			}
			strAll = sbAll.substring(0,sbAll.length()-1);
		} 
		if(!singleList.isEmpty()||singleList.size()!=0) {
			StringBuilder sbSingle = new StringBuilder();
			for(String s:singleList) {
				sbSingle.append("'");
				sbSingle.append(s);
				sbSingle.append("',");
			}
			strSingle = sbSingle.substring(0, sbSingle.length()-1);
		}
		
		List<Object> namelist = new ArrayList<Object>();
		List<List<Double>> datas = new ArrayList<>();
		List<Object> axisText = new ArrayList<Object>();
		
		int idx = 0;
		for(int i =0; i < 24;i++) {
			axisText.add(i+":00");
		}
		
		if (strAll!="") {
			sql1 = "SELECT CAST(AVG(speed) as decimal(18, 2)) as avg_speed,case when b.road_level = 1 then 'AllHighway' else 'AllExpressway' end AS english_name, substring(b.data,9,11) as hour " + 
					"FROM audi_speed_road a JOIN audi_speed_road_data b ON a.rtic_id=b.rtic_id " + 
					"WHERE b.road_level IN ("+level+") AND week='"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' " + 
					"AND a.city='"+cityName+"' GROUP BY b.road_level,substring(b.data,9,11) ORDER BY english_name,HOUR";
			
			try {
				List<Map<String, Object>> results1 = jdbc.queryForList(sql1);
				Map<String,Map<String,Double>> m1 = new LinkedHashMap<>();
				
				for(String s:allList) {
					m1.put(s, new LinkedHashMap<String,Double>());
				}
				
				
				for(Map<String,Object> m : results1) {
					String name = m.get("english_name").toString();
					
					double speed = Double.parseDouble(m.get("avg_speed").toString());
					
					String hour = m.get("hour").toString();
					
					Map<String,Double> m2 = m1.get(name);
					
					m2.put(hour, speed);
					
				}
				for(int i=0;i<allList.size();i++) {
					datas.add(new ArrayList<Double>());
				}
				
				Iterator<Entry<String, Map<String, Double>>> it = m1.entrySet().iterator();
				
				while(it.hasNext()) {
					Entry<String, Map<String, Double>> en = it.next();
					String key = en.getKey();
					
					namelist.add(key);
					
					Map<String, Double> value = en.getValue();
					
					Iterator<Entry<String, Double>> it2 = value.entrySet().iterator();
					
					while(it2.hasNext()) {
						Entry<String, Double> en2 = it2.next();
						double avg_speed = en2.getValue();
						datas.get(idx).add(avg_speed);
					}
					idx++;
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		if(strSingle!="") {
			sql2 = " SELECT CAST(AVG(speed) as decimal(18, 2)) as avg_speed,b.english_name, substring(a.data,9,11) as hour " + 
					"FROM audi_speed_road_data a JOIN audi_speed_road b ON a.rtic_id=b.rtic_id " + 
					"WHERE b.english_name IN("+strSingle+") AND week='"+week+"' AND substring(data,1,8) BETWEEN '"+sDate+"' AND '"+eDate+"' " + 
					"AND b.city='"+cityName+"' GROUP BY b.english_name,substring(a.data,9,11) ORDER BY english_name, HOUR"; 
			try {
				List<Map<String, Object>> results2 = jdbc.queryForList(sql2);
				
				Map<String,Map<String,Double>> m1 = new LinkedHashMap<>();
				
				for(String s:singleList) {
					m1.put(s, new LinkedHashMap<String,Double>());
				}
				
				
				for(Map<String,Object> m : results2) {
					String name = m.get("english_name").toString();
					
					double speed = Double.parseDouble(m.get("avg_speed").toString());
					
					String hour = m.get("hour").toString();
					
					Map<String,Double> m2 = m1.get(name);
					
					m2.put(hour, speed);
					
				}
				for(int i=0;i<singleList.size();i++) {
					datas.add(new ArrayList<Double>());
				}
				
				
				Iterator<Entry<String, Map<String, Double>>> it = m1.entrySet().iterator();
				
				while(it.hasNext()) {
					Entry<String, Map<String, Double>> en = it.next();
					String key = en.getKey();
					
					namelist.add(key);
					
					Map<String, Double> value = en.getValue();
					
					Iterator<Entry<String, Double>> it2 = value.entrySet().iterator();
					
					while(it2.hasNext()) {
						Entry<String, Double> en2 = it2.next();
						double avg_speed = en2.getValue();
						datas.get(idx).add(avg_speed);
					}
					idx++;
				}
				
			
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
			
			 HttpHeaders h = new HttpHeaders();  
			   h.add("Content-Type", "text/csv; charset=utf-8");  
			   h.setContentDispositionFormData("filename", "avgspeed.csv");
			   StringBuilder sBuilder = new StringBuilder();
			   sBuilder.append("CityName,RoadName,Weekday,0:00,1:00,2:00,3:00,4:00,5:00,6:00,7:00,8:00,9:00,10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00,18:00,19:00,20:00,21:00,22:00,23:00");
			   sBuilder.append("\n");
			   for (int i = 0; i<namelist.size();i++) {
				   sBuilder.append(cityName);
				   sBuilder.append(",");
				   sBuilder.append(namelist.get(i));
				   sBuilder.append(",");
				   sBuilder.append(week);
				   sBuilder.append(",");
				   for(int j = 0; j < datas.get(i).size();j++) {
					   sBuilder.append(datas.get(i).get(j));
					   sBuilder.append(",");
					   
				   }
				   sBuilder.append("\n");
			   }
		return new ResponseEntity<>(sBuilder.toString().getBytes("utf-8"), h, HttpStatus.OK); 
	}
	
}
